<?php
namespace app\admin\controller;

use think\facade\Db;
use think\facade\View;
use think\Request;
use think\facade\Config;
use think\facade\Session;
class Data extends Base
{
    protected $request;
    public function __construct(Request $request)
    {
        $this->request=$request;
    }

    public function index(){
        $Db = Db::connect();
        $tables = $Db->query('SHOW TABLE STATUS');

        $tables = array_map('array_change_key_case', $tables);
        $total = 0;
        foreach ($tables as $k=>$v){
            $tables[$k]['size']=format_bytes($v['data_length']+$v['index_length']);
            $total+=(int)$v['data_length']+(int)['index_length'];
        }
        $total=format_bytes($total);
        $tableNum=count($tables);
        View::assign([
            'tables'=>$tables,
            'tableNum'=>$tableNum,
            'total'=>$total
        ]);
        return view('index');
    }
    public function import(){
        //列出备份文件列表
        $path_tmp = Config::get('app.data_backup_path');
        is_dir($path_tmp) || mkdir($path_tmp, 0755, true);
        $path = realpath($path_tmp);
        $flag = \FilesystemIterator::KEY_AS_FILENAME;
        $glob = new \FilesystemIterator($path, $flag);

        $list = array();
        $filenum = $total = 0;
        foreach ($glob as $name => $file) {
            if (preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql(?:\.gz)?$/', $name)) {
                $name = sscanf($name, '%4s%2s%2s-%2s%2s%2s-%d');
                $date = "{$name[0]}-{$name[1]}-{$name[2]}";
                $time = "{$name[3]}:{$name[4]}:{$name[5]}";
                $part = $name[6];
                $info = pathinfo($file);
                if (isset($list["{$date} {$time}"])) {
                    $info = $list["{$date} {$time}"];
                    $info['part'] = max($info['part'], $part);
                    $info['size'] = $info['size'] + $file->getSize();
                } else {
                    $info['part'] = $part;
                    $info['size'] = $file->getSize();
                }
                $extension = strtoupper(pathinfo($file->getFilename(), PATHINFO_EXTENSION));
                $info['compress'] = ($extension === 'SQL') ? '-' : $extension;
                $info['time'] = strtotime("{$date} {$time}");
                $list["{$date} {$time}"] = $info;
                $filenum++;
                $total += $info['size'];
            }
        }
        $list=array_reverse($list);

        View::assign([
            'data'=>$list,
            'filenum'=>$filenum,
            'total'=>$total
        ]);
        return view('import');
    }
    /**
     * 优化表
     * @param  String $ids 表名
     */
    public function optimize($ids = null) {

        if (empty($ids)) {
            return json(['code'=>0,'msg'=>'请指定要优化的表!']);
        }
        $Db = Db::connect();
        if (is_array($ids)) {
            $tables = implode('`,`', $ids);
            $list = $Db->query("OPTIMIZE TABLE `{$tables}`");
            if($list){
                return json(['code'=>1,'msg'=>'数据表优化完成!']);
            } else {
                return json(['code'=>0,'msg'=>'数据表优化出错请重试!']);
            }
        } else {

            $list = $Db->query("OPTIMIZE TABLE `{$ids}`");
            if($list){
                return json(['code'=>1,'msg'=>"数据表{$ids}优化完成！"]);
            } else {
                return json(['code'=>0,'msg'=>"数据表{$ids}优化出错请重试！"]);
            }
        }
    }

    /**
     * 修复表
     * @param  String $ids 表名
     * @author [dashang]
     */
    public function repair($ids = null) {
        if (empty($ids)) {
            return json(['code'=>0,'msg'=>'请指定要修复的表!']);
        }
        $Db = Db::connect();
        if (is_array($ids)) {
            $tables = implode('`,`', $ids);
            $list = $Db->query("REPAIR TABLE `{$tables}`");
            if($list){
                return json(['code'=>1,'msg'=>'数据表修复完成!']);
            } else {
                return json(['code'=>0,'msg'=>'数据表修复出错请重试!']);
            }
        } else {
            $list = $Db->query("REPAIR TABLE `{$ids}`");
            if($list){
                return json(['code'=>1,'msg'=>"数据表{$ids}修复完成！"]);
            } else {
                return json(['code'=>0,'msg'=>"数据表{$ids}修复出错请重试！"]);
            }
        }
    }

    /**
     * 查看表结构
     * @param null $tables
     * @return \think\response\View
     */
    public function columns($tables=null){
        $Db = Db::connect();
        $columns=$Db->query("show full columns from {$tables}");
        View::assign([
            'columns'=>$columns,
            'tables'=>$tables
        ]);
        return view('columns');
    }

    /**
     * 备份数据库
     * @param  String  $ids 表名
     * @param  Integer $id     表ID
     * @param  Integer $start  起始行数
     * @author [dashang]
     */
    public function export($ids = null, $id = null, $start = null) {

        if ($this->request->isPost() && !empty($ids) && is_array($ids)) { //初始化
            $path = Config::get('app.data_backup_path');
            is_dir($path) || mkdir($path, 0755, true);
            //读取备份配置
            $config = [
                'path' => realpath($path) . DIRECTORY_SEPARATOR,
                'part' => Config::get('app.data_backup_part_size'),
                'compress' => Config::get('app.data_backup_compress'),
                'level' => Config::get('app.data_backup_compress_level'),
            ];


            //检查是否有正在执行的任务
            $lock = "{$config['path']}backup.lock";
            if (is_file($lock)) {
                return json(['code'=>0,'msg'=>'检测到有一个备份任务正在执行，请稍后再试！']);
            }
            file_put_contents($lock, $this->request->time()); //创建锁文件
            //检查备份目录是否可写
            is_writeable($config['path']) || $this->error('备份目录不存在或不可写，请检查后重试！');
            Session::set('backup_config', $config);
            //生成备份文件信息
            $file = [
                'name' => date('Ymd-His', $this->request->time()),
                'part' => 1,
            ];
            Session::set('backup_file', $file);
            //缓存要备份的表
            Session::set('backup_tables', $ids);
            //创建备份文件
            $Database = new \data\Database($file, $config);
            if (false !== $Database->create()) {
                $tab = ['id' => 0, 'start' => 0];
                return json(['code'=>1,'msg'=>'初始化成功','data'=>['tables' => $ids, 'tab' => $tab]]);
            } else {
                return json(['code'=>0,'msg'=>'初始化失败，备份文件创建失败！']);
            }
        } elseif ($this->request->isGet() && is_numeric($id) && is_numeric($start)) { //备份数据
            $tables = Session::get('backup_tables');
            //备份指定表
            $Database = new \data\Database(Session::get('backup_file'), Session::get('backup_config'));
            $start = $Database->backup($tables[(int) $id], $start);
            if (false === $start) { //出错
                return json(['code'=>0,'msg'=>'备份出错!']);
            } elseif (0 === $start) { //下一表
                if (isset($tables[++$id])) {
                    $tab = ['id' => $id, 'start' => 0];
                    return json(['code'=>1,'msg'=>'备份完成！','data'=>['tab' => $tab]]);
                } else { //备份完成，清空缓存
                    unlink(Session::get('backup_config.path') . 'backup.lock');
                    Session::set('backup_tables', null);
                    Session::set('backup_file', null);
                    Session::set('backup_config', null);
                    return json(['code'=>1,'msg'=>'备份完成！','data'=>'']);
                }
            } else {
                $tab = ['id' => $id, 'start' => $start[0]];
                $rate = floor(100 * ($start[0] / $start[1]));
                return json(['code'=>1,'msg'=>"正在备份...({$rate}%)",'data'=>['tab' => $tab]]);
            }
        } else {
            return json(['code'=>0,'msg'=>'请选择要备份的数据表!']);
        }
    }

    public function del($time = 0) {
        empty($time) && $this->error('参数错误！');
        $name = date('Ymd-His', $time) . '-*.sql*';
        $path = realpath(Config::get('app.data_backup_path')) . DIRECTORY_SEPARATOR . $name;
        array_map("unlink", glob($path));
        if (count(glob($path))) {
            return json(['code'=>0,'msg'=>'备份文件删除失败，请检查权限！']);
        } else {
            return json(['code'=>1,'msg'=>'备份文件删除成功！']);
        }
    }
    /**
     * 还原数据库
     * @author [dashang]
     */
    public function revert($time = 0, $part = null, $start = null) {
        if (is_numeric($time) && is_null($part) && is_null($start)) { //初始化
            //获取备份文件信息
            $name = date('Ymd-His', $time) . '-*.sql*';

            $path = realpath(Config::get('app.data_backup_path')) . DIRECTORY_SEPARATOR . $name;
            $files = glob($path);

            $list = [];
            foreach ($files as $name) {
                $basename = basename($name);
                $match = sscanf($basename, '%4s%2s%2s-%2s%2s%2s-%d');
                $gz = preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql.gz$/', $basename);
                $list[$match[6]] = array($match[6], $name, $gz);
            }
            ksort($list);
            $last = end($list);//检测文件正确性

            if (count($list) === $last[0]) {
                Session::set('backup_list', $list); //缓存备份列表
                return json(['code'=>1,'msg'=>'初始化完成,请等待！','data'=>['part' => 1, 'start' => 0]]);
            } else {
                return json(['code'=>0,'msg'=>'备份文件可能已经损坏，请检查！','data'=>'']);
            }
        } elseif (is_numeric($part) && is_numeric($start)) {
            $list = Session::get('backup_list');
            $db = new \data\Database($list[$part], [
                    'path' => realpath(Config::get('app.data_backup_path')) . DIRECTORY_SEPARATOR,
                    'compress' => $list[$part][2]
                ]
            );

            $start = $db->import($start);
            if (false === $start) {
                return json(['code'=>0,'msg'=>'还原数据出错！','data'=>'']);

            } elseif (0 === $start) { //下一卷
                if (isset($list[++$part])) {
                    $data = array('part' => $part, 'start' => 0);
                    return json(['code'=>1,'msg'=>"正在还原...#{$part}",'data'=>$data]);
                } else {
                    Session::set('backup_list', null);
                    return json(['code'=>1,'msg'=>"数据库还原完成",'data'=>'']);
                }
            } else {
                $data = array('part' => $part, 'start' => $start[0]);
                if ($start[1]) {
                    $rate = floor(100 * ($start[0] / $start[1]));
                    return json(['code'=>1,'msg'=>"正在还原...#{$part} ({$rate}%)",'data'=>$data]);

                } else {
                    $data['gz'] = 1;
                    return json(['code'=>1,'msg'=>"正在还原...#{$part}",'data'=>$data]);
                }
            }
        } else {
            return json(['code'=>0,'msg'=>"参数错误",'data'=>'']);
        }
    }


    public function line(){
        $Db = Db::connect();
        $tables = $Db->query('SHOW TABLE STATUS');

        View::assign([
            'tables'=>$tables
        ]);
        return view('line');
    }

    public function query(){
        $do_action = $this->request->post('do_action');

        echo '<style type="text/css">
            xmp,body{margin:0;padding:0;line-height:18px;font-size:12px;font-family:"Helvetica Neue", Helvetica, Microsoft Yahei, Hiragino Sans GB, WenQuanYi Micro Hei, sans-serif;}
            hr{height:1px;margin:5px 1px;background:#e3e3e3;border:none;}
            </style>';
        if ($do_action == '') {
            echo "操作错误!";
            die();
        }

        $tablename = $this->request->post("tablename/a");


        if (in_array($do_action, array('doquery', 'optimizeall', 'repairall'))) {
            $this->$do_action();
        } elseif (!isset($tablename)) {
            die("请选择要操作的表!");
        } else {
            foreach ($tablename as $table) {
                $this->$do_action($table);
                echo "<br />";
            }
        }
    }

    private function optimizes($name = '')
    {
        if (Db::execute("OPTIMIZE TABLE `{$name}`")) {
            echo "优化表【".$name."】成功";
        } else {
            echo "优化表【".$name."】失败";
        }
    }
    private function repairs($name = '')
    {
        if (Db::execute("REPAIR TABLE `{$name}`")) {
            echo "修复表【".$name."】成功";
        } else {
            echo "修复表【".$name."】失败";;
        }
    }
    private function viewinfo($name)
    {
        $row = Db::query("SHOW CREATE TABLE `{$name}`");
        $row = array_values($row[0]);
        $info = $row[1];
        echo "<xmp>{$info};</xmp>";
    }
    private function viewdata($name = '')
    {
        $sqlquery = "SELECT * FROM `{$name}`";
        $this->doquery($sqlquery);
    }

    private function doquery($sql = null)
    {
        $sqlquery = $sql ? $sql : $this->request->post('sqlquery');
        if ($sqlquery == '') {
            exit("SQL语句不能为空!");
        }
        $sqlquery = str_replace("\r", "", $sqlquery);
        $sqls = preg_split("/;[ \t]{0,}\n/i", $sqlquery);
        $maxreturn = 100;
        $r = '';
        foreach ($sqls as $key => $val) {
            if (trim($val) == '') {
                continue;
            }
            $val = rtrim($val, ';');
            $r .= "SQL：<span style='color:green;'>{$val}</span> ";
            if (preg_match("/^(select|explain)(.*)/i ", $val)) {
              
                $begin=microtime_float();
                $limit = stripos(strtolower($val), "limit") !== false ? true : false;
                $count = Db::execute($val);
                if ($count > 0) {
                    $resultlist = Db::query($val . (!$limit && $count > $maxreturn ? ' LIMIT ' . $maxreturn : ''));
                } else {
                    $resultlist = [];
                }
                $end=microtime_float();
                $time=$end-$begin;

                $usedseconds = "耗时".number_format($time,2)."秒!" . "<br />";
                if ($count <= 0) {
                    $r .= "返回结果为空!";
                } else {
                    $r .= "共有".$count."条记录!". (!$limit && $count > $maxreturn ? ',' . "最大返回".$maxreturn."条" : "");
                }
                $r = $r . ',' . $usedseconds;
                $j = 0;
                foreach ($resultlist as $m => $n) {
                    $j++;
                    if (!$limit && $j > $maxreturn) {
                        break;
                    }
                    $r .= "<hr/>";
                    $r .= "<font color='red'>" . "记录：".$j . "</font><br />";
                    foreach ($n as $k => $v) {
                        $r .= "<font color='blue'>{$k}：</font>{$v}<br/>\r\n";
                    }
                }
            } else {
                $begin=time();
                $count = Db::execute($val);
                $end=time();
                $time=$end-$begin;
                $r .= "共影响".$count."条记录! 耗时：".number_format($time,2)."秒!". "<br />";
            }
        }
        echo $r;
    }
}